<!DOCTYPE html>
<html lang="zh-Hans">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 3.8.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"yoursite.com","root":"/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="规范alibabaMysql开发规范和常见问题处理参考1 TodoItemSHOW ENGINE INNODB STATUS; todo建模工具PDMan todo还是可视化视图清晰点，还可以导出自动生成sql。 Basic Knowledge常用命令12345678910111213141516171819202122232425262728# 设置主键自增初始值alter table t_in">
<meta name="keywords" content="Mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql系列之备忘录">
<meta property="og:url" content="http://yoursite.com/2019/10/15/Mysql系列之备忘录/index.html">
<meta property="og:site_name" content="Feng&#39;s Blog">
<meta property="og:description" content="规范alibabaMysql开发规范和常见问题处理参考1 TodoItemSHOW ENGINE INNODB STATUS; todo建模工具PDMan todo还是可视化视图清晰点，还可以导出自动生成sql。 Basic Knowledge常用命令12345678910111213141516171819202122232425262728# 设置主键自增初始值alter table t_in">
<meta property="og:locale" content="zh-Hans">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/43t5XR.png">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/l55Uqw.png">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/l55Uqw.png">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/otmOCJ.png">
<meta property="og:updated_time" content="2020-07-30T15:42:46.861Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql系列之备忘录">
<meta name="twitter:description" content="规范alibabaMysql开发规范和常见问题处理参考1 TodoItemSHOW ENGINE INNODB STATUS; todo建模工具PDMan todo还是可视化视图清晰点，还可以导出自动生成sql。 Basic Knowledge常用命令12345678910111213141516171819202122232425262728# 设置主键自增初始值alter table t_in">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/43t5XR.png">

<link rel="canonical" href="http://yoursite.com/2019/10/15/Mysql系列之备忘录/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-Hans'
  };
</script>

  <title>Mysql系列之备忘录 | Feng's Blog</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="Toggle navigation bar">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">Feng's Blog</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>Home</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>About</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>Tags</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>Categories</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>Archives</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/10/15/Mysql系列之备忘录/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          Mysql系列之备忘录
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>

              <time title="Created: 2019-10-15 21:36:49" itemprop="dateCreated datePublished" datetime="2019-10-15T21:36:49+08:00">2019-10-15</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">Edited on</span>
                <time title="Modified: 2020-07-30 23:42:46" itemprop="dateModified" datetime="2020-07-30T23:42:46+08:00">2020-07-30</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/Mysql/" itemprop="url" rel="index"><span itemprop="name">Mysql</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h1 id="规范"><a href="#规范" class="headerlink" title="规范"></a>规范</h1><p><a href="https://help.aliyun.com/wordpower/2596091-1.html" target="_blank" rel="noopener">alibabaMysql开发规范和常见问题处理</a><br><a href="https://learnku.com/articles/25116" target="_blank" rel="noopener">参考1</a></p>
<h1 id="TodoItem"><a href="#TodoItem" class="headerlink" title="TodoItem"></a>TodoItem</h1><h2 id="SHOW-ENGINE-INNODB-STATUS-todo"><a href="#SHOW-ENGINE-INNODB-STATUS-todo" class="headerlink" title="SHOW ENGINE INNODB STATUS; todo"></a>SHOW ENGINE INNODB STATUS; todo</h2><h2 id="建模工具PDMan-todo"><a href="#建模工具PDMan-todo" class="headerlink" title="建模工具PDMan todo"></a>建模工具PDMan todo</h2><p>还是可视化视图清晰点，还可以导出自动生成sql。</p>
<h1 id="Basic-Knowledge"><a href="#Basic-Knowledge" class="headerlink" title="Basic Knowledge"></a>Basic Knowledge</h1><h2 id="常用命令"><a href="#常用命令" class="headerlink" title="常用命令"></a>常用命令</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"># 设置主键自增初始值</span><br><span class="line">alter table t_ins_sequence AUTO_INCREMENT = 100000;</span><br><span class="line"># 修改表名</span><br><span class="line">alter table sys_user_role rename to sys_user_role_rel;</span><br><span class="line"># 查询数据库里面各个表的大小</span><br><span class="line">SELECT CONCAT(table_schema,&apos;.&apos;,table_name) AS &apos;Table Name&apos;,</span><br><span class="line">    table_rows AS &apos;Number of Rows&apos;,</span><br><span class="line">    CONCAT(ROUND(data_length/(1024*1024*1024),6),&apos; G&apos;) AS &apos;Data Size&apos;,</span><br><span class="line">    CONCAT(ROUND(index_length/(1024*1024*1024),6),&apos; G&apos;) AS &apos;Index Size&apos; ,</span><br><span class="line">    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),&apos; G&apos;) AS&apos;Total&apos;</span><br><span class="line">FROM information_schema.TABLES</span><br><span class="line">WHERE table_schema LIKE &apos;inspect_db&apos;;</span><br><span class="line"># 修改主键自增</span><br><span class="line">alter table word_fetch_queue modify queue_id int auto_increment;</span><br><span class="line"># 修改主键被外键约束show create table table_name;</span><br><span class="line"></span><br><span class="line">SET FOREIGN_KEY_CHECKS = 0;</span><br><span class="line"># 约束解除，进行修改，完了还原</span><br><span class="line">SET FOREIGN_KEY_CHECKS = 1;</span><br><span class="line"># 查询表的建表DDL</span><br><span class="line">show create table table_name;</span><br><span class="line"># 查看表空间模式</span><br><span class="line">show variables like &apos;%innodb_file_per_table%&apos; ;</span><br><span class="line"># Mysql基本可以将schema当成database；下面二个命令都会创建对应数据库</span><br><span class="line">create schema xxxName;</span><br><span class="line">create databse xxxName;</span><br><span class="line"># 查询某个字符开头的所有表</span><br><span class="line">show tables like &apos;ACT_%&apos;;</span><br></pre></td></tr></table></figure>
<h2 id="执行计划EXPLAIN"><a href="#执行计划EXPLAIN" class="headerlink" title="执行计划EXPLAIN"></a>执行计划EXPLAIN</h2><p><a href="https://www.cnblogs.com/tufujie/p/9413852.html" target="_blank" rel="noopener">Mysql执行计划教程</a></p>
<h2 id="Mysql的级联更新"><a href="#Mysql的级联更新" class="headerlink" title="Mysql的级联更新"></a>Mysql的级联更新</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">update tb1,</span><br><span class="line">tb2</span><br><span class="line">set tb1.a=tb2.a,tb1.b=tb2.b</span><br><span class="line">where tb1.id=tb2.id;</span><br><span class="line"></span><br><span class="line">update tb1,</span><br><span class="line">(select * from tb2 where xxx) as tb2</span><br><span class="line">set tb1.a=tb2.a,tb1.b=tb2.b</span><br><span class="line">where tb1.id=tb2.id;</span><br><span class="line"></span><br><span class="line">update (select * from tb1 group by C) as temp,</span><br><span class="line">       tb2,</span><br><span class="line">       tb1</span><br><span class="line">set tb1.a = tb2.a,</span><br><span class="line">    tb1.b = tb2.b</span><br><span class="line">where tb1.id = tb2.id and</span><br><span class="line">      temp.id = tb2.id;</span><br><span class="line"></span><br><span class="line">update tb1 left join tb2 on tb1.id = tb2.id</span><br><span class="line">set tb1.a = tb2.a,</span><br><span class="line">    tb1.b = tb2.b</span><br><span class="line">where xxx;</span><br></pre></td></tr></table></figure>
<h2 id="Mysql和Oracle的Sql的不同之处"><a href="#Mysql和Oracle的Sql的不同之处" class="headerlink" title="Mysql和Oracle的Sql的不同之处"></a>Mysql和Oracle的Sql的不同之处</h2><p>delete在oracle中是支持别名的，mysql不支持<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">delete from table_name tn;// mysql error</span><br></pre></td></tr></table></figure></p>
<p>Oracle可以直接sequence，Mysql不能，如果非要在Mysql使用类似Oracle的sequence功能，可以通过一张表来模拟，参考：<br><a href="https://blog.csdn.net/u012373815/article/details/78179411" title="标题" target="_blank" rel="noopener">https://blog.csdn.net/u012373815/article/details/78179411</a></p>
<p>Mysql没有类似Oracle一样的表空间概念，mysql的表空间分为两个文件一个是<em>.ibd文件存储数据索引…等数据信息，且只针对一张表，还有一个文件是</em>.frm这个文件是存储表的元数据。</p>
<h2 id="time、timestamp、date、datetime"><a href="#time、timestamp、date、datetime" class="headerlink" title="time、timestamp、date、datetime"></a>time、timestamp、date、datetime</h2><p>DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.</p>
<p>DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.</p>
<p>TIMESTAMP: It is used for values that contain both date and time parts. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.</p>
<p>TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).</p>
<h2 id="char-vs-varchar"><a href="#char-vs-varchar" class="headerlink" title="char vs varchar"></a>char vs varchar</h2><p>MySQL - CHAR vs VARCHAR - What is the difference? CHAR is fixed length while VARCHAR is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces. A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.</p>
<h2 id="Mysql一对多数据显示成一行"><a href="#Mysql一对多数据显示成一行" class="headerlink" title="Mysql一对多数据显示成一行"></a>Mysql一对多数据显示成一行</h2><p><a href="https://www.cnblogs.com/lucky-girl/p/9482169.html" target="_blank" rel="noopener">使用GROUP_CONCAT</a></p>
<h2 id="Mysql常用函数大全"><a href="#Mysql常用函数大全" class="headerlink" title="Mysql常用函数大全"></a>Mysql常用函数大全</h2><p><a href="https://www.cnblogs.com/panchanggui/p/10652322.html" target="_blank" rel="noopener">常用函数</a></p>
<h2 id="数据库的导入导出（数据迁移、数据备份恢复）"><a href="#数据库的导入导出（数据迁移、数据备份恢复）" class="headerlink" title="数据库的导入导出（数据迁移、数据备份恢复）"></a>数据库的导入导出（数据迁移、数据备份恢复）</h2><h3 id="快速备份某个表"><a href="#快速备份某个表" class="headerlink" title="快速备份某个表"></a>快速备份某个表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLE IF EXISTS B;</span><br><span class="line">CREATE TABLE B LIKE A;</span><br><span class="line">INSERT INTO B SELECT * FROM A;</span><br></pre></td></tr></table></figure>
<h3 id="导出备份单个表"><a href="#导出备份单个表" class="headerlink" title="导出备份单个表"></a>导出备份单个表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -u root -p dbname tablename &gt; table.sql</span><br></pre></td></tr></table></figure>
<h3 id="导出备份整个库"><a href="#导出备份整个库" class="headerlink" title="导出备份整个库"></a>导出备份整个库</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -u root -p dbname &gt; db.sql</span><br></pre></td></tr></table></figure>
<h3 id="导入通过mysql命令"><a href="#导入通过mysql命令" class="headerlink" title="导入通过mysql命令"></a>导入通过mysql命令</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql -u root -p vocabulary_enhancer_db &lt; /mysql_sql_temp/old_bak.sql</span><br></pre></td></tr></table></figure>
<h3 id="临时表备份"><a href="#临时表备份" class="headerlink" title="临时表备份"></a>临时表备份</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;</span><br></pre></td></tr></table></figure>
<h2 id="所有子查询都要用唯一别名"><a href="#所有子查询都要用唯一别名" class="headerlink" title="所有子查询都要用唯一别名"></a>所有子查询都要用唯一别名</h2><h2 id="主键插入返回"><a href="#主键插入返回" class="headerlink" title="主键插入返回"></a>主键插入返回</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">&lt;insert id=&quot;genSequence&quot; parameterType=&quot;com.ccssoft.ngbip.sdk.core.entity.Sequence&quot;&gt;</span><br><span class="line">    insert into $&#123;tableName&#125; (stub) values (&apos;b&apos;)</span><br><span class="line">    &lt;selectKey resultType=&quot;integer&quot; keyProperty=&quot;id&quot;&gt;</span><br><span class="line">        select last_insert_id() as id</span><br><span class="line">        &lt;!-- select &apos;NEXT VALUE FOR MYCATSEQ_$&#123;tableName&#125;&apos; as id --&gt;</span><br><span class="line">    &lt;/selectKey&gt;</span><br><span class="line">&lt;/insert&gt;</span><br></pre></td></tr></table></figure>
<p><a href="https://blog.csdn.net/czd3355/article/details/71302441/" target="_blank" rel="noopener">https://blog.csdn.net/czd3355/article/details/71302441/</a></p>
<h1 id="表设计"><a href="#表设计" class="headerlink" title="表设计"></a>表设计</h1><h2 id="外键设计"><a href="#外键设计" class="headerlink" title="外键设计"></a>外键设计</h2><p>尽量避免使用外键，如果一定要使用外键的情况下，必须要考虑是否要在主表加上ON DELETE CASCADE或者ON UPDATE CASCADE，大部分情况下是必须要加上这个级联约束的，但是要注意的是，关系表中的外键，一定要建索引，否则在删除主键时级联删除外键会很慢很慢。</p>
<h2 id="主键使用uuid和自增int的区别"><a href="#主键使用uuid和自增int的区别" class="headerlink" title="主键使用uuid和自增int的区别"></a>主键使用uuid和自增int的区别</h2><p><a href="http://www.mysqltutorial.org/mysql-uuid/" target="_blank" rel="noopener">http://www.mysqltutorial.org/mysql-uuid/</a><br><a href="https://juejin.im/post/5c32a7a86fb9a049ad7734b3" target="_blank" rel="noopener">https://juejin.im/post/5c32a7a86fb9a049ad7734b3</a><br>最近在重构公司一个老的模块，重构当然要从数据库开始着手，关于数据表主键的实现方式选择，我想到的有二种方式，一种是uuid，一种是自增int，<br>先抛开是否分布式架构不说，选用uuid网上都会提到两个明显的弊端：<br>1、是数据暂用空间大.<br>2、数据无序性对索引这种树结构的存储有性能上的影响。</p>
<p>第一点我倒是觉得没什么大的影响，现在的硬盘已经不是缺那点点存储的问题了。<br>选用自增int的话，也有两个明显的弊端：<br>1、存在安全性问题，将一个已知主键加减某个值就得到另外一个主键了。<br>2、如果多个表数据合并，可以自增主键存在冲突。</p>
<p>真到了分布式的可以有第三方成熟的实现方案可供选择。<br>结论：尽量使用整型做主键，数据无序性的开销太大了，这点足以让整型的弊端再多，也必须竭力去解决弊端后选择整型做主键。</p>
<h2 id="主键int还是bigint"><a href="#主键int还是bigint" class="headerlink" title="主键int还是bigint"></a>主键int还是bigint</h2><p>int的范围：从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。<br>bigint的范围：从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。</p>
<p>int的上限已经很大了，一般的应用不会超出这个范围，所以主键一般使用int，也可以节省存储。</p>
<h2 id="分布式主键自增实现方案"><a href="#分布式主键自增实现方案" class="headerlink" title="分布式主键自增实现方案"></a>分布式主键自增实现方案</h2><p><a href="https://tech.meituan.com/2017/04/21/mt-leaf.html" target="_blank" rel="noopener">美团Leaf</a><br><a href="https://www.cnblogs.com/relucent/p/4955340.html" target="_blank" rel="noopener">twitter的snowflake</a></p>
<h2 id="创建和更新时间"><a href="#创建和更新时间" class="headerlink" title="创建和更新时间"></a>创建和更新时间</h2><p>必要时，尽量在表里面添加创建时间（create_time）和更新事件（update_time）</p>
<h2 id="表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用"><a href="#表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用" class="headerlink" title="表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用"></a>表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用</h2><p><a href="https://www.jianshu.com/p/47a1d7998fe0" target="_blank" rel="noopener">https://www.jianshu.com/p/47a1d7998fe0</a></p>
<h2 id="Mysql-的表尽量设置成-K-V-Key-Value-结构，什么情况下适合设计成K-V结构？"><a href="#Mysql-的表尽量设置成-K-V-Key-Value-结构，什么情况下适合设计成K-V结构？" class="headerlink" title="Mysql 的表尽量设置成 K-V (Key-Value) 结构，什么情况下适合设计成K-V结构？"></a>Mysql 的表尽量设置成 K-V (Key-Value) 结构，什么情况下适合设计成K-V结构？</h2><p><a href="https://blog.csdn.net/zcl_love_wx/article/details/81178339" target="_blank" rel="noopener">表Key-value结构介绍</a><br>在存储一些数据结构经常变动的数据适合采用K-V结构。</p>
<h2 id="为什么要尽量避免表字段为NULL值"><a href="#为什么要尽量避免表字段为NULL值" class="headerlink" title="为什么要尽量避免表字段为NULL值"></a>为什么要尽量避免表字段为NULL值</h2><p>表字段避免 null 值出现，null 值很难查询优化且占用额外的索引空间，推荐默认数字 0 代替 null。<br><img src="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/43t5XR.png" alt></p>
<h2 id="字段类型的选择"><a href="#字段类型的选择" class="headerlink" title="字段类型的选择"></a>字段类型的选择</h2><ul>
<li>尽量使用 INT 而非 BIGINT，如果非负则加上 UNSIGNED（这样数值容量会扩大一倍），当然能使用 TINYINT、SMALLINT、MEDIUM_INT 更好。</li>
<li>使用整数代替字符串类型</li>
<li><a href="https://cloud.tencent.com/developer/news/155166" target="_blank" rel="noopener">枚举类型的弊端</a></li>
</ul>
<h2 id="IP使用整型来存储"><a href="#IP使用整型来存储" class="headerlink" title="IP使用整型来存储"></a>IP使用整型来存储</h2><p><a href="https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/" target="_blank" rel="noopener">为什么推荐使用</a><br>注意最后一段：<br>The load on MySQL when inserting integer IPs could likely be slightly reduced by doing that conversion in your application, rather than using MySQL’s INET_ATON() function.<br>ip的转换可以考虑放到应用程序来做，而不是通过Mysql的函数做转换。</p>
<h2 id="Mysql的分库分表，水平拆分-垂直拆分-todo"><a href="#Mysql的分库分表，水平拆分-垂直拆分-todo" class="headerlink" title="Mysql的分库分表，水平拆分-垂直拆分 todo"></a>Mysql的分库分表，水平拆分-垂直拆分 todo</h2><h2 id="共享表空间和独享表空间的区别和优缺点"><a href="#共享表空间和独享表空间的区别和优缺点" class="headerlink" title="共享表空间和独享表空间的区别和优缺点"></a>共享表空间和独享表空间的区别和优缺点</h2><p><a href="http://blog.itpub.net/15498/viewspace-2124040/" target="_blank" rel="noopener">http://blog.itpub.net/15498/viewspace-2124040/</a></p>
<h2 id="单表大小限制与表空间的关系"><a href="#单表大小限制与表空间的关系" class="headerlink" title="单表大小限制与表空间的关系"></a>单表大小限制与表空间的关系</h2><p><a href="https://blog.csdn.net/wsh900221/article/details/80242299" target="_blank" rel="noopener">https://blog.csdn.net/wsh900221/article/details/80242299</a></p>
<h2 id="timestamp-默认当前时间不能同时是not-null"><a href="#timestamp-默认当前时间不能同时是not-null" class="headerlink" title="timestamp 默认当前时间不能同时是not null"></a>timestamp 默认当前时间不能同时是not null</h2><h1 id="命名"><a href="#命名" class="headerlink" title="命名"></a>命名</h1><ul>
<li>索引使用_ind结尾，字段的多个单词取单词的首字母组合，例如：sample 表 member_id 上的索引:sample_mid_ind。</li>
<li>主键约束: pk 结尾，_pk;</li>
<li>unique 约束:_uk 结尾，uk;</li>
<li>check 约束: _ck 结尾，ck;</li>
<li>外键约束: _fk 结尾，以 pri 连接本表与主表，_pri_fk;</li>
<li>数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的 命名方式是为了明确其取值含义与取值范围;</li>
<li>表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只<br>出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑;</li>
</ul>
<h2 id="Mysql关键字大全"><a href="#Mysql关键字大全" class="headerlink" title="Mysql关键字大全"></a>Mysql关键字大全</h2><p><a href="https://blog.csdn.net/weixin_43201975/article/details/88953903" target="_blank" rel="noopener">https://blog.csdn.net/weixin_43201975/article/details/88953903</a></p>
<h1 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h1><h2 id="为什么推荐外键约束放在应用层，不在数据库库"><a href="#为什么推荐外键约束放在应用层，不在数据库库" class="headerlink" title="为什么推荐外键约束放在应用层，不在数据库库?"></a>为什么推荐外键约束放在应用层，不在数据库库?</h2><p><a href="https://learnku.com/articles/25116" target="_blank" rel="noopener">https://learnku.com/articles/25116</a></p>
<h1 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h1><ul>
<li>索引并不是越多越好，要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描</li>
<li>应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描</li>
<li>值分布很稀少的字段不适合建索引，例如 “性别” 这种只有两三个值的字段</li>
<li>字符字段太长的话，考虑用前缀索引</li>
<li>字符字段最好不要做主键 - 超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时， 保证被关联的字段需要有索引。(即使双表 join 也要注意表索引、SQL 性能)</li>
</ul>
<h2 id="前缀索引"><a href="#前缀索引" class="headerlink" title="前缀索引"></a>前缀索引</h2><p><a href="https://blog.csdn.net/ma2595162349/article/details/79449493" target="_blank" rel="noopener">前缀索引解释</a><br>如果是对已经存在的表建前缀索引，要先去比较重复率再择优创建索引：<br><img src="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/l55Uqw.png" alt></p>
<h2 id="联合索引之最左原则（左前缀原则）"><a href="#联合索引之最左原则（左前缀原则）" class="headerlink" title="联合索引之最左原则（左前缀原则）"></a>联合索引之最左原则（左前缀原则）</h2><p><a href="https://blog.csdn.net/wdjxxl/article/details/79790421" target="_blank" rel="noopener">https://blog.csdn.net/wdjxxl/article/details/79790421</a><br><a href="https://blog.csdn.net/Wuhaotian1996/article/details/80469353" target="_blank" rel="noopener">https://blog.csdn.net/Wuhaotian1996/article/details/80469353</a></p>
<h2 id="多对多的关联表索引"><a href="#多对多的关联表索引" class="headerlink" title="多对多的关联表索引"></a>多对多的关联表索引</h2><p>一般是在二个关联的主键字典建唯一联合索引，避免重复数据，提升查询效率。</p>
<h2 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a>覆盖索引</h2><p>Order by的时候，如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须扫表。</p>
<h2 id="order-by出现file-sort"><a href="#order-by出现file-sort" class="headerlink" title="order by出现file_sort"></a>order by出现file_sort</h2><p><a href="https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html" target="_blank" rel="noopener">order by 官方的优化文档</a><br><a href="https://www.cnblogs.com/developer_chan/p/9225638.html" target="_blank" rel="noopener">Order by 优化</a><br>没有使用到索引来排序是，执行计划会显示file_sort。<br>需要重点注意的是，索引创建是也是有排序的，默认是ASC，如果Order by的排序和索引创建的时候指定的不一致，也会导致file_sort。  </p>
<p>非联合索引不受order by后面的升序和降序影响到是否使用索引排序，联合索引的情况下应该考虑order  by的升序降序是否和索引创建时一致。</p>
<h2 id="时间类字段是否应该建索引"><a href="#时间类字段是否应该建索引" class="headerlink" title="时间类字段是否应该建索引"></a>时间类字段是否应该建索引</h2><p>我自己测试了一下建索引确实是性能上有明显提升的，<br>没加索引：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[2019-12-25 15:13:59] 1 row retrieved starting from 1 in 592 ms (execution: 576 ms, fetching: 16 ms)</span><br></pre></td></tr></table></figure></p>
<p><img src="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/l55Uqw.png" alt><br>加上索引之后：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[2019-12-25 15:19:28] 1 row retrieved starting from 1 in 258 ms (execution: 239 ms, fetching: 19 ms)</span><br></pre></td></tr></table></figure></p>
<p><img src="https://cdn.jsdelivr.net/gh/coding-by-feng/oss@master/uPic/otmOCJ.png" alt></p>
<h2 id="什么情况下SQL不会使用到索引"><a href="#什么情况下SQL不会使用到索引" class="headerlink" title="什么情况下SQL不会使用到索引"></a>什么情况下SQL不会使用到索引</h2><ul>
<li>like出现左匹配：’%XXX’</li>
<li>where语句中使用&lt;&gt;, !=, not in</li>
<li>where语句中使用 or，但是没有把or中所有字段加上索引，这种情况，如果需要使用索引需要将or中所有的字段都加上索引。</li>
<li>where语句中对字段表达式操作</li>
<li>使用索引的时候没有遵从索引的左前缀原则</li>
</ul>
<h2 id="SQL编写优化"><a href="#SQL编写优化" class="headerlink" title="SQL编写优化"></a>SQL编写优化</h2><ul>
<li>避免 select <em>，将需要查找的字段列出来，这里并不是因为select </em> 就一定是性能明显更差的原因，具体解释参考<a href="https://blog.csdn.net/qq_38836118/article/details/88708933" target="_blank" rel="noopener">https://blog.csdn.net/qq_38836118/article/details/88708933</a></li>
</ul>
<h2 id="SQL手工拼写的防止注入"><a href="#SQL手工拼写的防止注入" class="headerlink" title="SQL手工拼写的防止注入"></a>SQL手工拼写的防止注入</h2><p>varchar类型的字段，在拼写条件值得时候，外面用单引号包围起来，如：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">+ objId + &quot; and o.disp_object_type=&apos;&quot; + objType + &quot;&apos;&quot;);</span><br></pre></td></tr></table></figure></p>
<h1 id="Problem-Solution"><a href="#Problem-Solution" class="headerlink" title="Problem Solution"></a>Problem Solution</h1><h2 id="The-last-packet-sent-successfully-to-the-server-was-0-milliseconds-ago-The-driver-has-not-received-any-packets-from-the-server"><a href="#The-last-packet-sent-successfully-to-the-server-was-0-milliseconds-ago-The-driver-has-not-received-any-packets-from-the-server" class="headerlink" title="The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server."></a>The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.</h2><p>自己出现这个问题是因为连公司内部库是用vpn，vpn连续开了2天，连接mysql就报了这个错，vpn重启一下就ok了。</p>
<h2 id="required-a-bean-of-type-‘org-springframework-cache-CacheManager’-that-could-not-be-found"><a href="#required-a-bean-of-type-‘org-springframework-cache-CacheManager’-that-could-not-be-found" class="headerlink" title="required a bean of type ‘org.springframework.cache.CacheManager’ that could not be found."></a>required a bean of type ‘org.springframework.cache.CacheManager’ that could not be found.</h2><p>需要再spring.foctories添加org.springframework.boot.autoconfigure.EnableAutoConfiguration配置，<br>至少需要配置RedisTemplate</p>
<h2 id="ERROR-2002-HY000-Can’t-connect-to-local-MySQL-server-through-socket-‘-tmp-mysql-sock’-111"><a href="#ERROR-2002-HY000-Can’t-connect-to-local-MySQL-server-through-socket-‘-tmp-mysql-sock’-111" class="headerlink" title="ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)"></a>ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)</h2><p>可能是数据库服务资源紧张挂掉了，mysqld进程我已查看都不在了，重启mysql就好了，当然也有其他原因也会导致这个报错。</p>
<h2 id="delete语句子查询not-exists是不会生效的"><a href="#delete语句子查询not-exists是不会生效的" class="headerlink" title="delete语句子查询not exists是不会生效的"></a>delete语句子查询not exists是不会生效的</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">delete</span><br><span class="line">from word_paraphrase_star_rel</span><br><span class="line">where not exists(select * from word_paraphrase wp where wp.paraphrase_id = paraphrase_id);</span><br></pre></td></tr></table></figure>
<p>需要改成：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">delete</span><br><span class="line">from word_paraphrase_star_rel</span><br><span class="line">where paraphrase_id in (select tmp.paraphrase_id</span><br><span class="line">                        from (select wpsr.paraphrase_id</span><br><span class="line">                              from word_paraphrase_star_rel wpsr</span><br><span class="line">                              where not exists(</span><br><span class="line">                                      select *</span><br><span class="line">                                      from word_paraphrase wp</span><br><span class="line">                                      where wp.paraphrase_id = wpsr.paraphrase_id)) tmp)</span><br></pre></td></tr></table></figure></p>
<h2 id="You-can’t-specify-target-table-‘word-paraphrase-star-rel’-for-update-in-FROM-clause"><a href="#You-can’t-specify-target-table-‘word-paraphrase-star-rel’-for-update-in-FROM-clause" class="headerlink" title="You can’t specify target table ‘word_paraphrase_star_rel’ for update in FROM clause"></a>You can’t specify target table ‘word_paraphrase_star_rel’ for update in FROM clause</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">delete</span><br><span class="line">from word_paraphrase_star_rel</span><br><span class="line">where paraphrase_id in (select wpsr.paraphrase_id</span><br><span class="line">                        from word_paraphrase_star_rel wpsr</span><br><span class="line">                        where not exists(</span><br><span class="line">                                select * from word_paraphrase wp where wp.paraphrase_id = wpsr.paraphrase_id))</span><br></pre></td></tr></table></figure>
<p>改成下面即可：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">delete</span><br><span class="line">from word_paraphrase_star_rel</span><br><span class="line">where paraphrase_id in (select tmp.paraphrase_id</span><br><span class="line">                        from (select wpsr.paraphrase_id</span><br><span class="line">                              from word_paraphrase_star_rel wpsr</span><br><span class="line">                              where not exists(</span><br><span class="line">                                      select *</span><br><span class="line">                                      from word_paraphrase wp</span><br><span class="line">                                      where wp.paraphrase_id = wpsr.paraphrase_id)) tmp)</span><br></pre></td></tr></table></figure></p>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/Mysql/" rel="tag"># Mysql</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2019/10/15/Redis汇总/" rel="prev" title="Redis汇总">
      <i class="fa fa-chevron-left"></i> Redis汇总
    </a></div>
      <div class="post-nav-item">
    <a href="/2019/10/17/WebStorm汇总/" rel="next" title="WebStorm汇总">
      WebStorm汇总 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          Table of Contents
        </li>
        <li class="sidebar-nav-overview">
          Overview
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#规范"><span class="nav-number">1.</span> <span class="nav-text">规范</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#TodoItem"><span class="nav-number">2.</span> <span class="nav-text">TodoItem</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#SHOW-ENGINE-INNODB-STATUS-todo"><span class="nav-number">2.1.</span> <span class="nav-text">SHOW ENGINE INNODB STATUS; todo</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#建模工具PDMan-todo"><span class="nav-number">2.2.</span> <span class="nav-text">建模工具PDMan todo</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Basic-Knowledge"><span class="nav-number">3.</span> <span class="nav-text">Basic Knowledge</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#常用命令"><span class="nav-number">3.1.</span> <span class="nav-text">常用命令</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#执行计划EXPLAIN"><span class="nav-number">3.2.</span> <span class="nav-text">执行计划EXPLAIN</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql的级联更新"><span class="nav-number">3.3.</span> <span class="nav-text">Mysql的级联更新</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql和Oracle的Sql的不同之处"><span class="nav-number">3.4.</span> <span class="nav-text">Mysql和Oracle的Sql的不同之处</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#time、timestamp、date、datetime"><span class="nav-number">3.5.</span> <span class="nav-text">time、timestamp、date、datetime</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#char-vs-varchar"><span class="nav-number">3.6.</span> <span class="nav-text">char vs varchar</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql一对多数据显示成一行"><span class="nav-number">3.7.</span> <span class="nav-text">Mysql一对多数据显示成一行</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql常用函数大全"><span class="nav-number">3.8.</span> <span class="nav-text">Mysql常用函数大全</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数据库的导入导出（数据迁移、数据备份恢复）"><span class="nav-number">3.9.</span> <span class="nav-text">数据库的导入导出（数据迁移、数据备份恢复）</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#快速备份某个表"><span class="nav-number">3.9.1.</span> <span class="nav-text">快速备份某个表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#导出备份单个表"><span class="nav-number">3.9.2.</span> <span class="nav-text">导出备份单个表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#导出备份整个库"><span class="nav-number">3.9.3.</span> <span class="nav-text">导出备份整个库</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#导入通过mysql命令"><span class="nav-number">3.9.4.</span> <span class="nav-text">导入通过mysql命令</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#临时表备份"><span class="nav-number">3.9.5.</span> <span class="nav-text">临时表备份</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#所有子查询都要用唯一别名"><span class="nav-number">3.10.</span> <span class="nav-text">所有子查询都要用唯一别名</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#主键插入返回"><span class="nav-number">3.11.</span> <span class="nav-text">主键插入返回</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#表设计"><span class="nav-number">4.</span> <span class="nav-text">表设计</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#外键设计"><span class="nav-number">4.1.</span> <span class="nav-text">外键设计</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#主键使用uuid和自增int的区别"><span class="nav-number">4.2.</span> <span class="nav-text">主键使用uuid和自增int的区别</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#主键int还是bigint"><span class="nav-number">4.3.</span> <span class="nav-text">主键int还是bigint</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#分布式主键自增实现方案"><span class="nav-number">4.4.</span> <span class="nav-text">分布式主键自增实现方案</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#创建和更新时间"><span class="nav-number">4.5.</span> <span class="nav-text">创建和更新时间</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用"><span class="nav-number">4.6.</span> <span class="nav-text">表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql-的表尽量设置成-K-V-Key-Value-结构，什么情况下适合设计成K-V结构？"><span class="nav-number">4.7.</span> <span class="nav-text">Mysql 的表尽量设置成 K-V (Key-Value) 结构，什么情况下适合设计成K-V结构？</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#为什么要尽量避免表字段为NULL值"><span class="nav-number">4.8.</span> <span class="nav-text">为什么要尽量避免表字段为NULL值</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#字段类型的选择"><span class="nav-number">4.9.</span> <span class="nav-text">字段类型的选择</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#IP使用整型来存储"><span class="nav-number">4.10.</span> <span class="nav-text">IP使用整型来存储</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql的分库分表，水平拆分-垂直拆分-todo"><span class="nav-number">4.11.</span> <span class="nav-text">Mysql的分库分表，水平拆分-垂直拆分 todo</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#共享表空间和独享表空间的区别和优缺点"><span class="nav-number">4.12.</span> <span class="nav-text">共享表空间和独享表空间的区别和优缺点</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#单表大小限制与表空间的关系"><span class="nav-number">4.13.</span> <span class="nav-text">单表大小限制与表空间的关系</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#timestamp-默认当前时间不能同时是not-null"><span class="nav-number">4.14.</span> <span class="nav-text">timestamp 默认当前时间不能同时是not null</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#命名"><span class="nav-number">5.</span> <span class="nav-text">命名</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql关键字大全"><span class="nav-number">5.1.</span> <span class="nav-text">Mysql关键字大全</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#约束"><span class="nav-number">6.</span> <span class="nav-text">约束</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#为什么推荐外键约束放在应用层，不在数据库库"><span class="nav-number">6.1.</span> <span class="nav-text">为什么推荐外键约束放在应用层，不在数据库库?</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#索引"><span class="nav-number">7.</span> <span class="nav-text">索引</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#前缀索引"><span class="nav-number">7.1.</span> <span class="nav-text">前缀索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#联合索引之最左原则（左前缀原则）"><span class="nav-number">7.2.</span> <span class="nav-text">联合索引之最左原则（左前缀原则）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#多对多的关联表索引"><span class="nav-number">7.3.</span> <span class="nav-text">多对多的关联表索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#覆盖索引"><span class="nav-number">7.4.</span> <span class="nav-text">覆盖索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#order-by出现file-sort"><span class="nav-number">7.5.</span> <span class="nav-text">order by出现file_sort</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#时间类字段是否应该建索引"><span class="nav-number">7.6.</span> <span class="nav-text">时间类字段是否应该建索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#什么情况下SQL不会使用到索引"><span class="nav-number">7.7.</span> <span class="nav-text">什么情况下SQL不会使用到索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL编写优化"><span class="nav-number">7.8.</span> <span class="nav-text">SQL编写优化</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL手工拼写的防止注入"><span class="nav-number">7.9.</span> <span class="nav-text">SQL手工拼写的防止注入</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#Problem-Solution"><span class="nav-number">8.</span> <span class="nav-text">Problem Solution</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#The-last-packet-sent-successfully-to-the-server-was-0-milliseconds-ago-The-driver-has-not-received-any-packets-from-the-server"><span class="nav-number">8.1.</span> <span class="nav-text">The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#required-a-bean-of-type-‘org-springframework-cache-CacheManager’-that-could-not-be-found"><span class="nav-number">8.2.</span> <span class="nav-text">required a bean of type ‘org.springframework.cache.CacheManager’ that could not be found.</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#ERROR-2002-HY000-Can’t-connect-to-local-MySQL-server-through-socket-‘-tmp-mysql-sock’-111"><span class="nav-number">8.3.</span> <span class="nav-text">ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#delete语句子查询not-exists是不会生效的"><span class="nav-number">8.4.</span> <span class="nav-text">delete语句子查询not exists是不会生效的</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#You-can’t-specify-target-table-‘word-paraphrase-star-rel’-for-update-in-FROM-clause"><span class="nav-number">8.5.</span> <span class="nav-text">You can’t specify target table ‘word_paraphrase_star_rel’ for update in FROM clause</span></a></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">Fengorz</p>
  <div class="site-description" itemprop="description">A Quiet Departure.</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">125</span>
          <span class="site-state-item-name">posts</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">68</span>
        <span class="site-state-item-name">categories</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">87</span>
        <span class="site-state-item-name">tags</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Fengorz</span>
</div>
  <div class="powered-by">Powered by <a href="https://hexo.io" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a>
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>
<script src="/js/utils.js"></script><script src="/js/motion.js"></script>
<script src="/js/schemes/pisces.js"></script>
<script src="/js/next-boot.js"></script>



  















  

  

</body>
</html>
